package com.sailfish.springbootdemo.service.db7.Impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sailfish.springbootdemo.dao.db7.*;
import com.sailfish.springbootdemo.pojo.Result;
import com.sailfish.springbootdemo.pojo.db7.*;
import com.sailfish.springbootdemo.service.db7.CommonService;
import com.sailfish.springbootdemo.utils.ResultUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Slf4j
@Service
public class CommonServiceImpl implements CommonService {
    @Value("${personal.config.file.file-save-url}")
    private String rootPath;
    @Autowired
    private TbTemplateDao tbTemplateDao;
    @Autowired
    private TbTemplateVersionDao tbTemplateVersionDao;
    @Autowired
    private TbTaskTemplateDataDao tbTaskTemplateDataDao;
    @Autowired
    private TbKnowledgeBaseFileDao tbKnowledgeBaseFileDao;
    @Autowired
    private TbTaskVersionDao tbTaskVersionDao;

    @Override
    public List<TbDataDictionary> getAllChildren(List<TbDataDictionary> listAll, List<TbDataDictionary> listDicParent) {
        List<TbDataDictionary> result = new ArrayList<>();

        for (TbDataDictionary parent : listDicParent) {
            result.add(parent);
            result.addAll(getChildren(listAll, parent));
        }

        return result;
    }

    @Override
    public List<TbDataDictionary> getChildren(List<TbDataDictionary> listAll, TbDataDictionary parent) {
        List<TbDataDictionary> result = new ArrayList<>();

        for (TbDataDictionary child : listAll) {
            if (child == null || child.getParent_param_key() == null) {
                continue;
            }
            if (child.getParent_param_key().equals(parent.getParam_key())) {
                result.add(child);
                result.addAll(getChildren(listAll, child));
            }
        }

        return result;
    }

    @Override
    public List<TbDataDictionary> buildTree(List<TbDataDictionary> list) {
        List<TbDataDictionary> tree = new ArrayList<>();
        Map<Integer, TbDataDictionary> map = new HashMap<>();

        // 将所有节点放入 map 中，以便快速查找
        for (TbDataDictionary node : list) {
            map.put(node.getId(), node);
        }

        // 假设根节点的 id 为 0，你可以根据实际情况修改
        int rootId = 0;

        // 遍历所有节点，将每个节点添加到其父节点的 children 列表中
        for (TbDataDictionary node : list) {
            if (node.getParent_id() == rootId) {
                // 如果当前节点是根节点，直接添加到树中
                tree.add(node);
            } else {
                // 如果有父节点，则将其添加到父节点的 children 列表中
                TbDataDictionary parent = map.get(node.getParent_id());
                if (parent != null) {
                    if (parent.getChildren() == null) {
                        // 如果 children 列表为 null，则创建一个新的 ArrayList
                        parent.setChildren(new ArrayList<>());
                    }
                    parent.getChildren().add(node);
                    parent.setChildrenCount(parent.getChildren().size());
                }
            }
        }

        return tree;
    }

    @Override
    public List<TbDataDictionary> buildTree(List<TbDataDictionary> list, String Param_key) {
        List<TbDataDictionary> tree = new ArrayList<>();
        Map<String, TbDataDictionary> map = new HashMap<>();

        // 将所有节点放入 map 中，以便快速查找
        for (TbDataDictionary node : list) {
            map.put(node.getParam_key(), node);
        }

        // 遍历所有节点，将每个节点添加到其父节点的 children 列表中
        for (TbDataDictionary node : list) {
            if (node.getParam_key().equals(Param_key)) {
                tree.add(node);
            } else {
                // 如果有父节点，则将其添加到父节点的 children 列表中
                TbDataDictionary parent = map.get(node.getParent_param_key());
                if (parent != null) {
                    if (parent.getChildren() == null) {
                        // 如果 children 列表为 null，则创建一个新的 ArrayList
                        parent.setChildren(new ArrayList<>());
                    }
                    parent.getChildren().add(node);
                }
            }
        }

        return tree;
    }

    @Override
    public List<TbTemplate> buildTree2(List<TbTemplate> list) {
        List<TbTemplate> tree = new ArrayList<>();
        Map<String, TbTemplate> map = new HashMap<>();

        // 将所有节点放入 map 中，以便快速查找
        for (TbTemplate node : list) {
            map.put(node.getParam_key(), node);
        }
        // 遍历所有节点，将每个节点添加到其父节点的 children 列表中
        for (TbTemplate node : list) {
            if (node.getParent_param_key() == null) {
                // 如果当前节点是根节点，直接添加到树中
                tree.add(node);
                addChildren(node, map);
            }
        }

        return tree;
    }

    private void addChildren(TbTemplate parent, Map<String, TbTemplate> map) {
        List<TbTemplate> children = new ArrayList<>();

        // 收集所有符合条件的子节点
        for (TbTemplate node : map.values()) {
            boolean isCondition1 = node.getParent_param_key() != null
                    && node.getParent_param_key().equals(parent.getParam_key());
            boolean isCondition2 = node.getIs_value() != null
                    && node.getIs_value() == 1
                    && node.getParent_select_id() != null
                    && node.getParent_select_id().equals(parent.getParam_key());

            if (isCondition1 || isCondition2) {
                children.add(node);
            }
        }

        // 按 id 从小到大排序
        //children.sort(Comparator.comparing(TbTemplate::getParent_id));
        children.sort(
                Comparator.comparing(TbTemplate::getSort, Comparator.nullsLast(Comparator.naturalOrder()))
                        .thenComparing(TbTemplate::getId, Comparator.nullsLast(Comparator.naturalOrder()))
                        .thenComparing(TbTemplate::getParent_id, Comparator.nullsLast(Comparator.naturalOrder()))
        );

        // 设置排序后的子节点列表
        parent.setChildren(children);
        parent.setChildrenCount(children.size());

        // 递归处理子节点的子节点
        for (TbTemplate child : children) {
            addChildren(child, map);
        }
    }

    @Override
    public List<TbTaskTemplateData> buildTree3(List<TbTaskTemplateData> list) {
        List<TbTaskTemplateData> tree = new ArrayList<>();
        Map<String, TbTaskTemplateData> map = new HashMap<>();

        // 将所有节点放入 map 中，以便快速查找
        for (TbTaskTemplateData node : list) {
            map.put(node.getParam_key(), node);
        }
        // 遍历所有节点，将每个节点添加到其父节点的 children 列表中
        for (TbTaskTemplateData node : list) {
            if (node.getParent_param_key() == null) {
                // 如果当前节点是根节点，直接添加到树中
                tree.add(node);
                addChildren2(node, map);
            }
        }

        return tree;
    }

    private void addChildren2(TbTaskTemplateData parent, Map<String, TbTaskTemplateData> map) {
        List<TbTaskTemplateData> children = new ArrayList<>();

        // 收集所有符合条件的子节点
        for (TbTaskTemplateData node : map.values()) {
            boolean isCondition1 = node.getParent_param_key() != null
                    && node.getParent_param_key().equals(parent.getParam_key());
            boolean isCondition2 = node.getIs_value() != null
                    && node.getIs_value() == 1
                    && node.getParent_select_id() != null
                    && node.getParent_select_id().equals(parent.getParam_key());

            if (isCondition1 || isCondition2) {
                children.add(node);
            }
        }

        // 按 id 从小到大排序
        //children.sort(Comparator.comparing(TbTaskTemplateData::getParent_id));
        children.sort(
                Comparator.comparing(TbTaskTemplateData::getSort, Comparator.nullsLast(Comparator.naturalOrder()))
                        .thenComparing(TbTaskTemplateData::getId, Comparator.nullsLast(Comparator.naturalOrder()))
                        .thenComparing(TbTaskTemplateData::getParent_id, Comparator.nullsLast(Comparator.naturalOrder()))
        );

        // 示例：按字段1升序 → 字段2降序 → 字段3升序
        //list.sort(
        //        Comparator.comparing(MyClass::getField1)
        //                .thenComparing(Comparator.comparing(MyClass::getField2).reversed())
        //                .thenComparing(MyClass::getField3)
        //);

        // 设置排序后的子节点列表（替换旧的子节点列表）
        parent.setChildren(children);
        parent.setChildrenCount(children.size());

        // 递归处理子节点的子节点
        for (TbTaskTemplateData child : children) {
            addChildren2(child, map);
        }

    }

    @Override
    public List<TbKnowledgeBaseData> buildTree4(List<TbKnowledgeBaseData> list) {
        List<TbKnowledgeBaseData> tree = new ArrayList<>();
        Map<String, TbKnowledgeBaseData> map = new HashMap<>();

        // 将所有节点放入 map 中，以便快速查找
        for (TbKnowledgeBaseData node : list) {
            map.put(node.getParam_key(), node);
        }
        // 遍历所有节点，将每个节点添加到其父节点的 children 列表中
        for (TbKnowledgeBaseData node : list) {
            if (node.getParent_param_key() == null) {
                // 如果当前节点是根节点，直接添加到树中
                tree.add(node);
                addChildren3(node, map);
            }
        }

        return tree;
    }

    private void addChildren3(TbKnowledgeBaseData parent, Map<String, TbKnowledgeBaseData> map) {
        List<TbKnowledgeBaseData> children = new ArrayList<>();

        // 收集所有符合条件的子节点
        for (TbKnowledgeBaseData node : map.values()) {
            boolean isCondition1 = node.getParent_param_key() != null
                    && node.getParent_param_key().equals(parent.getParam_key());
            boolean isCondition2 = node.getIs_value() != null
                    && node.getIs_value() == 1
                    && node.getParent_select_id() != null
                    && node.getParent_select_id().equals(parent.getParam_key());

            if (isCondition1 || isCondition2) {
                children.add(node);
            }
        }

        // 按 id 从小到大排序
        children.sort(Comparator.comparing(TbKnowledgeBaseData::getParent_id));

        // 设置排序后的子节点列表（替换旧的子节点列表）
        parent.setChildren2(children);
        parent.setChildrenCount(children.size());

        // 递归处理子节点的子节点
        for (TbKnowledgeBaseData child : children) {
            addChildren3(child, map);
        }

    }

    @Override
    public List<TbTemplate> buildTemplateTree(List<TbTemplate> list) {
        List<TbTemplate> tree = new ArrayList<>();
        Map<String, TbTemplate> map = new HashMap<>();

        // 将所有节点放入 map 中，以便快速查找
        for (TbTemplate node : list) {
            map.put(node.getParam_key(), node);
        }
        // 遍历所有节点，将每个节点添加到其父节点的 children 列表中
        for (TbTemplate node : list) {
            if (node.getIs_value() == 2 && node.getSelect_level() >= 1) {
                tree.add(node);
                addTemplateChildren(node, map);
            }
        }
        return tree;
    }

    private void addTemplateChildren(TbTemplate parent, Map<String, TbTemplate> map) {
        if (parent.getChildren() == null) {
            // 如果 children 列表为 null，则创建一个新的 ArrayList
            parent.setChildren(new ArrayList<>());
        }

        for (TbTemplate node : map.values()) {
            if (node.getParent_param_key().equals(parent.getParam_key())) {
                // 如果有子节点，则将其添加到父节点的 children 列表中
                parent.getChildren().add(node);
                parent.setChildrenCount(parent.getChildren().size());
                addTemplateChildren(node, map);
            }

            if (node.getIs_value() != null && node.getIs_value() == 1 && node.getParent_select_id() != null && node.getParent_select_id().equals(parent.getParam_key())) {
                // 如果有子节点，则将其添加到父节点的 children 列表中
                parent.getChildren().add(node);
                parent.setChildrenCount(parent.getChildren().size());
                addTemplateChildren(node, map);
            }

        }
    }

    @Override
    public Result getTaskTemplateData(String templateType, List<Map<String, String>> keyValueMaps) {
        try {
            if (StringUtils.isEmpty(templateType)) {
                log.error("templateType is null");
                return ResultUtil.error(500, "空的模板类型", "templateType is null", null);
            }
            TbTemplateVersion param = new TbTemplateVersion();
            param.setTemplate_type(templateType);
            List<TbTemplateVersion> list = tbTemplateVersionDao.getTemplateVersionList(param);
            // 使用Comparator来比较版本号
            Comparator<TbTemplateVersion> versionComparator = (o1, o2) -> {
                String v1 = o1.getVersion();
                String v2 = o2.getVersion();

                return compareVersions(v1, v2);
            };

            // 找出最新的模板版本
            TbTemplateVersion latestVersion = list.stream()
                    .max(versionComparator)
                    .orElse(null);

            if (latestVersion == null) {
                log.error("cannot find newest template");
                return ResultUtil.error(500, "找不到最新模板", "cannot find newest template", null);
            }
            log.info("latest version is " + latestVersion.getVersion());
            // 根据模板类型和前端的keyValueMaps进一步筛选查询结果
            // 所有key和value都为null相当于没有过滤
            // key不为空value为空相当于无效过滤，去掉
            boolean hasValidContidionts = true;
            List<Map<String, String>> filteredKeyValueMaps = keyValueMaps.stream()
                    .filter(map -> {
                        String key = map.get("key");
                        String value = map.get("value");
                        return key != null && !key.isEmpty() && value != null && !value.isEmpty();
                    })
                    .collect(Collectors.toList());
            if (filteredKeyValueMaps.size() == 0) {
                hasValidContidionts = false;
            }

            // 先过滤已归档流程
            List<String> archivedFlowList;

            List<TbTaskTemplateData> allTaskDataByTemplate = new ArrayList<>();
            // 逐个版本查询获取
            //List<TbTaskTemplateData> validData = new ArrayList<>();
            if ("designType".equals(templateType)) {
                archivedFlowList = tbTaskVersionDao.getArchivedFlows("design");
                if (archivedFlowList == null) {
                    log.error("type design cannot find archived flow");
                    return ResultUtil.error(500, "design类型没有已归档流程", "type design cannot find archived flow", null);
                }
                if (hasValidContidionts) {
                    // 如果查询键值对有效，直接根据流程号获取所有对象
                    List<String> archivedFlowSet = Optional.ofNullable(archivedFlowList)
                            .orElse(Collections.emptyList());
                    Set<String> commonFlowNos = findCommonFlowNos(filteredKeyValueMaps, archivedFlowSet);

                    // 使用交集中的流程号来获取相应数据并添加到 allTaskDataByTemplate 中
                    for (String no : commonFlowNos) {
                        // 添加所有有效流程的数据到 allTaskDataByTemplate
                        allTaskDataByTemplate.addAll(tbTaskTemplateDataDao.getTaskTemplateDataByFlowNo(no));
                    }
                } else {
                    // 如果无效，需要所有归档数据
                    for (String f : archivedFlowList) {
                        allTaskDataByTemplate.addAll(tbTaskTemplateDataDao.getTaskTemplateDataByFlowNo(f));
                    }
                }
            } else {
                List<TbTaskVersion> archivedTaskList = tbTaskVersionDao.getArchievdTasks("device");
                if (archivedTaskList == null) {
                    return ResultUtil.error(500, "device类型没有已归档流程", "type device has no archived flow", null);
                }
                Map<String, List<TbTaskVersion>> groupedByCompCode = Optional.ofNullable(archivedTaskList)
                        .orElse(Collections.emptyList())
                        .stream()
                        .filter(task -> task.getComp_code() != null && !task.getComp_code().isEmpty()) // 非空判断
                        .collect(Collectors.groupingBy(TbTaskVersion::getComp_code));

                // 找到每个分组中 sort 最大值对应的对象，并提取 flow_no
                List<String> maxSortFlowNos = groupedByCompCode.values().stream()
                        .map(taskVersions -> taskVersions.stream()
                                .max(Comparator.comparingInt(TbTaskVersion::getSort))
                                .orElse(null)) // 如果某个分组为空，则返回 null
                        .filter(Objects::nonNull) // 过滤掉可能的 null 值
                        .map(TbTaskVersion::getFlow_no)
                        .collect(Collectors.toList());
                if (hasValidContidionts) {
                    // 如果查询键值对有效，直接根据流程号获取所有对象

                    // 找到 flowNoList 和 archivedFlowList 的交集
                    Set<String> commonFlowNos = findCommonFlowNos(filteredKeyValueMaps, maxSortFlowNos);

                    // 使用交集中的流程号来获取相应数据并添加到 allTaskDataByTemplate 中
                    for (String no : commonFlowNos) {
                        List<TbTaskTemplateData> dataByFlowNo = tbTaskTemplateDataDao.getTaskTemplateDataByFlowNo(no);
                        if (dataByFlowNo != null) {
                            allTaskDataByTemplate.addAll(dataByFlowNo);
                        } else {
                            log.warn("No data found for flow_no: " + no);
                        }
                    }
                } else {

                    // 如果无效，需要所有归档数据
                    for (String f : maxSortFlowNos) {
                        allTaskDataByTemplate.addAll(tbTaskTemplateDataDao.getTaskTemplateDataByFlowNo(f));
                    }
                }
            }

            if (allTaskDataByTemplate.size() == 0) {
                log.error("template flow data is null");
                return ResultUtil.error(500, "没有任何流程数据", "template flow data is null", null);
            }

            // 当前最新模板的所有param_key表
            List<TbTemplate> paramKeysOfTemplate =
                    tbTemplateDao.getTbTemplateByVersion(latestVersion.getTemplate_type(), latestVersion.getVersion());

            List<TbTemplate> logTemplateList =
                    paramKeysOfTemplate.
                            stream().
                            filter(element -> "logInfo".equals(element.getParent_param_key())).collect(Collectors.toList());

            List<TbTemplate> applicationTemplateList =
                    paramKeysOfTemplate.
                            stream().
                            filter(element -> "application".equals(element.getParent_param_key())).collect(Collectors.toList());

            List<TbTemplate> descriptionTemplateList = paramKeysOfTemplate.
                    stream().
                    filter(element -> "description".equals(element.getParent_param_key())).collect(Collectors.toList());

            List<TbTemplate> baseTypeTemplateList = paramKeysOfTemplate.
                    stream().
                    filter(element -> templateType.equals(element.getParent_param_key())).collect(Collectors.toList());
            if (logTemplateList == null || applicationTemplateList == null || descriptionTemplateList == null || baseTypeTemplateList == null) {
                return ResultUtil.error(500, "最新模板参数字段缺失", "some param key is null in newest version", null);
            }
            // 获取最新模板所有列名集合
            List<String> logParamKeys = logTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());

            List<String> applicationParamKeys = applicationTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());

            List<String> descriptionParamKeys = descriptionTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());

            List<String> baseTypeParamKeys = baseTypeTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());
            // 使用 Collectors.groupingBy 进行分组
            Map<String, List<TbTaskTemplateData>> groupedByFlowNo = allTaskDataByTemplate.stream()
                    .collect(Collectors.groupingBy(TbTaskTemplateData::getFlow_no));

            log.info("flows match status '3' size is " + groupedByFlowNo.size());
            // 遍历每个分组并进行处理
            JSONArray result = new JSONArray();
            for (Map.Entry<String, List<TbTaskTemplateData>> entry : groupedByFlowNo.entrySet()) {
                log.info("cur flow_no is " + entry.getKey());
                int maxRow = 0;
                // 在这里处理每个分组
                List<TbTaskTemplateData> group = entry.getValue();

                // 日志信息
                List<TbTaskTemplateData> logTaskTemplateDataList = group.stream()
                        .filter(item -> "logInfo".equals(item.getParent_param_key())).collect(Collectors.toList());
                // 基本信息
                List<TbTaskTemplateData> baseTypeTaskTemplateDataList = group.stream()
                        .filter(item -> templateType.equals(item.getParent_param_key())).collect(Collectors.toList());
                // 应用场合
                List<TbTaskTemplateData> applicationTaskTemplateDataList = group.stream()
                        .filter(item -> "application".equals(item.getParent_param_key()))
                        .collect(Collectors.toList());

                // 正文描述
                List<TbTaskTemplateData> descriptionTaskTemplateDataList = group.stream()
                        .filter(item -> "description".equals(item.getParent_param_key())).collect(Collectors.toList());


                Integer maxRowSort = 0;

                // 查询当前流程最大行数
                maxRowSort = tbTaskTemplateDataDao.
                        getMaxRowSortByLimit(entry.getKey());
                if (maxRowSort == null || maxRowSort < 0) {
                    log.warn("current flow has no max row_sort,flow_no: " + entry.getKey());
                    continue;
                }
                maxRow = maxRowSort.intValue() + 1;

                JSONArray appArray = addToTemplateDataArray(applicationParamKeys, maxRow, applicationTaskTemplateDataList, true);
                // 再用查询条件筛选一次
                filtByKeyValueMaps(appArray, filteredKeyValueMaps);
                maxRow = Math.max(appArray.size(), 0);
                // 同上添加日志信息数据
                JSONArray logArray = addToTemplateDataArray(logParamKeys, maxRow, logTaskTemplateDataList, false);
                // 再用查询条件筛选一次
                filtByKeyValueMaps(logArray, filteredKeyValueMaps);
                maxRow = Math.max(logArray.size(), maxRow);
                // 添加基本信息数据
                JSONArray baseTypeArray = addToTemplateDataArray(baseTypeParamKeys, maxRow, baseTypeTaskTemplateDataList, false);
                // 再用查询条件筛选一次
                filtByKeyValueMaps(baseTypeArray, filteredKeyValueMaps);
                maxRow = Math.max(baseTypeArray.size(), maxRow);
                // 添加正文描述数据
                JSONArray descriptArray = addToTemplateDataArray(descriptionParamKeys, maxRow, descriptionTaskTemplateDataList, true);
                // 再用查询条件筛选一次
                filtByKeyValueMaps(descriptArray, filteredKeyValueMaps);
                maxRow = Math.max(descriptArray.size(), maxRow);

                // 补充可能空缺的行到maxRow
                addDataRowToMaxRow(appArray, applicationParamKeys, maxRow, true);
                addDataRowToMaxRow(logArray, logParamKeys, maxRow, false);
                addDataRowToMaxRow(descriptArray, descriptionParamKeys, maxRow, true);
                addDataRowToMaxRow(baseTypeArray, baseTypeParamKeys, maxRow, false);

                // 将每个jsonArray中的对应行组合成一行放入结果中
                JSONObject temp = new JSONObject();
                temp.put("flow_no", entry.getKey());
                temp.put("application", appArray);
                temp.put("logInfo", logArray);
                temp.put("description", descriptArray);
                temp.put("basicInfo", baseTypeArray);
                result.add(temp);

            }
            return ResultUtil.success(result);
        } catch (Exception e) {
            log.error("error: ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    // 对于流程中数据param_value是文件或者图片的类型，需要文件对象
    private List<TbKnowledgeBaseFile> specialParamTypeProcess(TbTaskTemplateData tbTaskTemplateData) {
        List<TbKnowledgeBaseFile> ret = new ArrayList<>();

        if (tbTaskTemplateData.getParam_type() == 3 || tbTaskTemplateData.getParam_type() == 4) {
            if (tbTaskTemplateData.getParam_value() != null) {
                List<String> fileList = Arrays.asList(tbTaskTemplateData.getParam_value().split(","));
                ret = tbKnowledgeBaseFileDao.getTbKnowledgeBaseFileByParams(fileList);
            }
        }
        return ret;
    }



    private void filtByKeyValueMaps(JSONArray result, List<Map<String, String>> keyValueMaps) {
        // 如果 result 或 keyValueMaps 为空或 null，直接返回，不做处理
        if (result == null || result.isEmpty() || keyValueMaps == null || keyValueMaps.isEmpty()) {
            return;
        }

        // 将 keyValueMaps 按 key 分组，相同 key 的 value 放入 Set
        Map<String, Set<String>> keyToValuesMap = new HashMap<String, Set<String>>();
        for (Map<String, String> condition : keyValueMaps) {
            String key = condition.get("key");
            String value = condition.get("value");
            keyToValuesMap.computeIfAbsent(key, k -> new HashSet<String>()).add(value);
        }

        // 使用索引遍历并移除不满足条件的元素
        for (int i = 0; i < result.size(); ) {
            Object obj = result.get(i);
            if (obj instanceof JSONObject) {
                JSONObject jsonObject = (JSONObject) obj;
                boolean allKeysMatch = true;

                // 检查每个不同的 key
                for (Map.Entry<String, Set<String>> entry : keyToValuesMap.entrySet()) {
                    String key = entry.getKey();
                    Set<String> values = entry.getValue();

                    // 如果 jsonObject 不包含该 key，不是当前分类的key，继续循环
                    if (!jsonObject.containsKey(key)) {
                        continue;
                    }

                    // 获取 jsonObject 中该 key 的值，并进行模糊匹配
                    String jsonValue = jsonObject.getString(key);
                    boolean anyValueMatch = false;
                    for (String value : values) {
                        if (!("".equals(jsonValue)) && jsonValue.contains(value)) {
                            anyValueMatch = true;
                            break; // 只要有一个 value 匹配即可
                        }
                    }

                    // 如果该 key 的所有 value 都不匹配，标记为不满足
                    if (!anyValueMatch) {
                        allKeysMatch = false;
                        break;
                    }
                }

                // 如果不满足所有条件，移除当前元素
                if (!allKeysMatch) {
                    result.remove(i);
                } else {
                    i++; // 只有保留元素时才递增索引
                }
            } else {
                // 如果不是 JSONObject，直接移除
                result.remove(i);
            }
        }
    }


    /**
     * 根据前端键值对列表条件迭代查询，找到最终重合的flowNo，并与已归档的flowNo集合做交集
     *
     * @param keyValueMaps    前端传入的键值对列表
     * @param archivedFlowList 已归档的flowNo列表
     * @return 符合所有条件且与已归档flowNo有交集的flowNo集合
     */
    public Set<String> findCommonFlowNos(List<Map<String, String>> keyValueMaps, List<String> archivedFlowList) {
        // 如果 keyValueMaps 为空或 null，返回空集
        if (keyValueMaps == null || keyValueMaps.isEmpty()) {
            return new HashSet<String>();
        }

        // 将 keyValueMaps 按 key 分组，相同 key 的 value 放入 Set
        Map<String, Set<String>> keyToValuesMap = new HashMap<String, Set<String>>();
        for (Map<String, String> condition : keyValueMaps) {
            String key = condition.get("key");
            String value = condition.get("value");
            keyToValuesMap.computeIfAbsent(key, k -> new HashSet<String>()).add(value);
        }

        // 存储每个不同 key 的 flow_no 集合
        Map<String, Set<String>> keyToFlowNosMap = new HashMap<String, Set<String>>();

        // 迭代每个不同的 key，查询符合条件的 flow_no
        for (Map.Entry<String, Set<String>> entry : keyToValuesMap.entrySet()) {
            String key = entry.getKey();
            Set<String> values = entry.getValue();
            Set<String> flowNosForKey = new HashSet<String>();

            // 对该 key 的每个 value 执行查询（OR 逻辑）
            for (String value : values) {
                // 使用 HashMap 替代 Map.of
                Map<String, String> condition = new HashMap<String, String>();
                condition.put("key", key);
                condition.put("value", value);

                List<TbTaskTemplateData> validData = tbTaskTemplateDataDao.getValidByCondition(condition);

                // 如果查询结果为空，继续下一个 value
                if (validData == null || validData.isEmpty()) {
                    continue;
                }

                // 提取 flow_no 并加入集合
                Set<String> currentFlowNos = validData.stream()
                        .map(TbTaskTemplateData::getFlow_no)
                        .filter(flowNo -> flowNo != null && !flowNo.isEmpty()) // 过滤空值
                        .collect(Collectors.toSet());
                flowNosForKey.addAll(currentFlowNos); // OR 逻辑：合并所有 value 的结果
            }

            // 如果该 key 没有任何 flow_no，返回空集
            if (flowNosForKey.isEmpty()) {
                return new HashSet<String>();
            }

            keyToFlowNosMap.put(key, flowNosForKey);
        }

        // 如果没有符合条件的 flow_no，返回空集
        if (keyToFlowNosMap.isEmpty()) {
            return new HashSet<String>();
        }

        // 计算所有不同 key 的 flow_no 交集（AND 逻辑）
        Set<String> commonFlowNos = keyToFlowNosMap.values().stream()
                .reduce((set1, set2) -> {
                    set1.retainAll(set2); // 取交集
                    return set1;
                })
                .orElse(new HashSet<String>());

        // 与 archivedFlowList 取交集
        List<String> archivedFlowSet = archivedFlowList != null ? archivedFlowList : Collections.<String>emptyList();
        Set<String> finalFlowNos = commonFlowNos.stream()
                .filter(archivedFlowSet::contains)
                .collect(Collectors.toSet());

        return finalFlowNos;
    }

    /**
     * 将任务模板数据添加到JSONArray中。
     *
     * @param paramKeys 期望的键列表，用于占位符
     * @param maxRow 最大行数
     * @param taskTemplateDataList 任务模板数据列表
     * @param special 是否按行特殊处理
     * @return 生成的JSONArray
     */
    private JSONArray addToTemplateDataArray(List<String> paramKeys, int maxRow,
                                             List<TbTaskTemplateData> taskTemplateDataList,
                                             boolean special) {
        // 检查输入数据是否有效
        if (taskTemplateDataList == null || taskTemplateDataList.isEmpty()) {
            return new JSONArray();
        }
        // 过滤掉row_sort为-1的模板数据
        taskTemplateDataList = taskTemplateDataList.stream()
                .filter(element -> !(new Integer(-1).equals(element.getRow_sort())))
                .collect(Collectors.toList());
        // 根据special参数选择不同的处理逻辑
        return special ? addSpecialTemplateData(paramKeys, maxRow, taskTemplateDataList)
                : addGlobalTemplateData(paramKeys, maxRow, taskTemplateDataList);
    }

    /**
     * 处理special为true的情况，按行分组并生成JSONArray。
     */
    private JSONArray addSpecialTemplateData(List<String> paramKeys, int maxRow,
                                             List<TbTaskTemplateData> taskTemplateDataList) {
        JSONArray jsonArray = new JSONArray();

        // 使用 LinkedHashMap 保持行顺序,按照row_sort保证行对应关系
        Map<Integer, List<TbTaskTemplateData>> rowDataMap = taskTemplateDataList.stream()
                .collect(Collectors.groupingBy(TbTaskTemplateData::getRow_sort,
                        LinkedHashMap::new, Collectors.toList()));

        for (int i = 0; i < maxRow; i++) {
            JSONObject rowJson = new JSONObject();
            List<TbTaskTemplateData> rowData = rowDataMap.get(i);

            if (rowData != null) {
                for (TbTaskTemplateData data : rowData) {
                    List<TbKnowledgeBaseFile> files = specialParamTypeProcess(data);
                    if(Objects.isNull(files) || files.size() == 0)
                    {
                        rowJson.put(data.getParam_key(), data.getParam_value());
                    }else {
                        rowJson.put(data.getParam_key(), files);
                    }
                }
            }else{
                break;
            }

            // 确保所有 paramKeys 都有对应的值，即使为空
            for (String paramKey : paramKeys) {
                rowJson.putIfAbsent(paramKey, null);
            }

            jsonArray.add(rowJson);
        }

        return jsonArray;
    }

    /**
     * 处理special为false的情况，生成全局数据并复制到每一行。
     */
    private JSONArray addGlobalTemplateData(List<String> paramKeys, int maxRow,
                                            List<TbTaskTemplateData> taskTemplateDataList) {
        // 创建全局数据模板
        JSONObject globalDataJson = new JSONObject();
        for (TbTaskTemplateData data : taskTemplateDataList) {
            List<TbKnowledgeBaseFile> files = specialParamTypeProcess(data);
            if(Objects.isNull(files) || files.size() == 0)
            {
                globalDataJson.put(data.getParam_key(), data.getParam_value());
            }else {
                globalDataJson.put(data.getParam_key(), files);
            }
        }

        // 为缺失的paramKeys添加空值
        for (String paramKey : paramKeys) {
            globalDataJson.putIfAbsent(paramKey, null);
        }

        // 为每一行复制全局数据
        JSONArray jsonArray = new JSONArray();
        jsonArray.add(globalDataJson);
//        for (int i = 0; i < maxRow; i++) {
//            // 使用 JSON 字符串进行深拷贝
//            JSONObject rowCopy = JSONObject.parseObject(JSONObject.toJSONString(globalDataJson));
//            jsonArray.add(rowCopy);
//        }
        return jsonArray;
    }

    private int compareVersions(String v1, String v2) {
        Pattern pattern = Pattern.compile("(\\d{4})_T(\\d+)");
        Matcher matcher1 = pattern.matcher(v1);
        Matcher matcher2 = pattern.matcher(v2);

        if (!matcher1.matches() || !matcher2.matches()) {
            throw new IllegalArgumentException("版本号格式不正确");
        }

        int year1 = Integer.parseInt(matcher1.group(1));
        int year2 = Integer.parseInt(matcher2.group(1));

        if (year1 != year2) {
            return Integer.compare(year1, year2);
        }

        int number1 = Integer.parseInt(matcher1.group(2));
        int number2 = Integer.parseInt(matcher2.group(2));

        return Integer.compare(number1, number2);
    }
    private TbTemplate findTemplateByParamKey(List<TbTemplate> allTemplates, String match) {
        if(StringUtils.isEmpty(match)){
            return null;
        }
        // 使用 Java Stream API 进行过滤并提取 id
        Optional<TbTemplate> matchingItem = allTemplates.stream()
                .filter(item -> match.equals(item.getParam_key()))  // 根据实际情况调整比较方式
                .findFirst();  // 只取第一个匹配项

        if (matchingItem.isPresent()) {
            return matchingItem.get();
        } else {
            return null;
        }
    }
    private void addDataRowToMaxRow(JSONArray array, List<String> paramKeys, int maxRow, boolean special)
    {
        if(special)
        {
            for(int i = array.size(); i < maxRow; i++)
            {
                JSONObject jsonDt = new JSONObject();
                for (String paramKey : paramKeys) {
                    jsonDt.putIfAbsent(paramKey, null);
                }
                array.add(jsonDt);
            }
        }else{
            for(int i = array.size(); i < maxRow; i++)
            {
                JSONObject jsonDt = new JSONObject(array.getJSONObject(i - 1));
                array.add(jsonDt);
            }
        }

    }


    @Override
    public Result outputParamTemplate(List<String> namesList)
    {
        try
        {
            if(namesList == null || namesList.size() == 0)
            {
                return ResultUtil.error(500, "namesList is null", null, null);
            }
            // 创建工作簿对象
            Workbook workbook = new XSSFWorkbook();
            // 创建工作表对象
            Sheet sheet = workbook.createSheet("Sheet1");
            XSSFCellStyle setBorder = getStyle1(workbook);
            XSSFCellStyle setBorder2 = getStyle2(workbook);
            XSSFCellStyle setFont = getStyleFont(workbook);
            SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
            Row rowTR = sheet.createRow(0);
            for(int i=0;i<namesList.size(); i++)
            {
                Cell cellTR0 = rowTR.createCell(i);
                cellTR0.setCellValue(namesList.get(i));
                cellTR0.setCellStyle(setFont);
            }

            // 导出Excel文件
            String filename = "VPL属性表模板" + String.valueOf(System.currentTimeMillis());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format2 = sdf.format(new Date());
            File folder = new File(rootPath + "knowledgeBase\\" + format2);
            if (!folder.isDirectory()) {
                folder.mkdirs();
            }
            String filePath = rootPath + "knowledgeBase\\" + format2 + "\\" + filename + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
            return ResultUtil.success(filePath);
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public Result outputParamTemplate2(Map<String, List<String>> namesList,String excelName)
    {
        try
        {
            if(namesList == null || namesList.size() == 0)
            {
                return ResultUtil.error(500, "namesList is null", null, null);
            }
            // 创建工作簿对象
            Workbook workbook = new XSSFWorkbook();
            // 创建工作表对象

            SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");

            for (Map.Entry<String, List<String>> entry : namesList.entrySet()) {
                String name = entry.getKey();
                List<String> value = entry.getValue();

                Sheet sheet = workbook.createSheet(name);
                XSSFCellStyle setBorder = getStyle1(workbook);
                XSSFCellStyle setBorder2 = getStyle2(workbook);
                XSSFCellStyle setFont = getStyleFont(workbook);

                Row rowTR = sheet.createRow(0);
                for(int i=0;i<value.size(); i++)
                {
                    Cell cellTR0 = rowTR.createCell(i);
                    cellTR0.setCellValue(value.get(i));
                    cellTR0.setCellStyle(setFont);
                }
                for(int j = 1; j < entry.getValue().size(); j++)
                {
                    sheet.setColumnWidth(j, 20 * 256);
                }
            }

            // 导出Excel文件
            String filename = excelName+ String.valueOf(System.currentTimeMillis());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format2 = sdf.format(new Date());
            File folder = new File(rootPath + "knowledgeBase\\" + format2);
            if (!folder.isDirectory()) {
                folder.mkdirs();
            }
            String filePath = rootPath + "knowledgeBase\\" + format2 + "\\" + filename + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
            return ResultUtil.success(filePath);
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }



    public XSSFCellStyle getStyle2(Workbook workbook) {
        XSSFCellStyle setBorder2 = (XSSFCellStyle) workbook.createCellStyle();
        setBorder2.setBorderBottom(BorderStyle.THIN);
        setBorder2.setBorderLeft(BorderStyle.THIN);
        setBorder2.setBorderRight(BorderStyle.THIN);
        setBorder2.setBorderTop(BorderStyle.THIN);
        setBorder2.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder2.setAlignment(HorizontalAlignment.LEFT);
        return setBorder2;
    }

    public XSSFCellStyle getStyle1(Workbook workbook) {
        XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();
        setBorder.setBorderBottom(BorderStyle.THIN);
        setBorder.setBorderLeft(BorderStyle.THIN);
        setBorder.setBorderRight(BorderStyle.THIN);
        setBorder.setBorderTop(BorderStyle.THIN);
        setBorder.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder.setAlignment(HorizontalAlignment.LEFT);
        //创建字体 加粗
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setBold(true);
        font.setFontHeight(11);
        setBorder.setFont(font);
        return setBorder;
    }

    public XSSFCellStyle getStyleFont(Workbook workbook) {
        XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();
        //创建字体 加粗
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setBold(true);
        font.setFontHeight(11);
        setBorder.setFont(font);
        return setBorder;
    }

    public XSSFCellStyle getStyleColor(Workbook workbook, short colorIndex) {
        XSSFCellStyle styleRed = (XSSFCellStyle) workbook.createCellStyle();
        styleRed.setBorderBottom(BorderStyle.THIN);
        styleRed.setBorderLeft(BorderStyle.THIN);
        styleRed.setBorderRight(BorderStyle.THIN);
        styleRed.setBorderTop(BorderStyle.THIN);
        styleRed.setVerticalAlignment(VerticalAlignment.CENTER);
        styleRed.setAlignment(HorizontalAlignment.LEFT);
        styleRed.setFillForegroundColor(colorIndex);
        styleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return styleRed;
    }


    @Override
    public double parseFieldWithDefault(String value) {
        if (value == null || value.isEmpty()) return 0.0;
        try {
            return Double.parseDouble(value);
        } catch (NumberFormatException e) {
            return 0.0; // 非法格式视为0
        }
    }
}
